package com.shb.jutils.commons;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * POI操作Excel97-2003的工具类
 * 
 * @author shb
 *
 */
public class POIUtil {
	private POIUtil() {
	}

	/**
	 * 将集合中的数据写入Excel2003表格
	 * 
	 * @param list
	 *            数据集合
	 * @param filePath
	 *            生成临时文件的路径
	 * @param fileName 临时文件名
	 * @return
	 */
	public static void writeExcel(List<List<String>> list, String filePath, String fileName) {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");

		// 设置字体和样式
		HSSFFont headFont = wb.createFont();
		headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		HSSFCellStyle headStyle = wb.createCellStyle();
		headStyle.setFont(headFont);
		headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		HSSFFont bodyFont = wb.createFont();
		bodyFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		HSSFCellStyle bodyStyle = wb.createCellStyle();
		bodyStyle.setFont(bodyFont);
		bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		// 计算行数和列数
		int rowCount = list.size();
		int columnCount = list.get(0).size();

		// 设置列宽
		for (int i = 0; i < columnCount; i++) {
			// sheet.autoSizeColumn(columnCount);
			sheet.setColumnWidth(i, 4000);
		}
		
		for(int i = 0;i < rowCount; i++){
			HSSFRow row = sheet.createRow(i);
			for(int j =0 ; j < columnCount; j++){
				HSSFCell cell = row.createCell(j);
				if(list.get(i).get(j) != null){
					cell.setCellValue(list.get(i).get(j) == null ? null : list.get(i).get(j).trim());
					if(i == 0){
						cell.setCellStyle(headStyle);
					}else {
						cell.setCellStyle(bodyStyle);
					}
				}
			}
		}
		
		//保存文件
		OutputStream os = null;
		try{
			File dir = new File(filePath);
			if(!dir.exists() || !dir.isDirectory()){
				dir.mkdirs();
			}
			File file = new File(filePath + "\\" + fileName);
			os = new FileOutputStream(file);
			wb.write(os);
		}catch(IOException e){
			throw new RuntimeException(e);
		}finally {
			if(wb != null){
				try {
					wb.close();
				} catch (IOException e) {
					wb = null;
				}
			}
			if(os != null){
				try {
					os.close();
				} catch (IOException e) {
					os = null;
				}
			}
		}
	}

	/**
	 * 读取Excel中内容
	 * @param in		Excel 97_2003文件流
	 * @param onlyFirstSheet	是否值读取第一个Sheet页
	 * @return
	 */
	public static List<List<String>> readExcel(InputStream in, boolean onlyFirstSheet){
		List<List<String>> listAll = new ArrayList<List<String>>(500);
		HSSFWorkbook wb = null;
		try {
			wb = new HSSFWorkbook(in);
		
			int sheetCount = onlyFirstSheet ? 1 : wb.getNumberOfSheets();
			for(int i = 0; i < sheetCount; i++){
				HSSFSheet sheet = wb.getSheetAt(i);
				int rowCount = sheet.getLastRowNum() +1;
				for(int j = 0; j < rowCount; j++){
					HSSFRow row = sheet.getRow(j);
					if(isEmptyRow(row)){
						continue;
					}
					int columnCount = row.getLastCellNum();
					List<String> list = new ArrayList<String>(columnCount);
					for(int k =0; k < columnCount; k++){
						HSSFCell cell = row.getCell(k);
						list.add(getStringCellValue(cell));
					}
					listAll.add(list);
				}
			}
		} catch (IOException e) {
			throw new RuntimeException(e);
		}finally{
			if(wb != null){
				try {
					wb.close();
				} catch (IOException e) {
					wb = null;
				}
			}
			if(in != null){
				try {
					in.close();
				} catch (IOException e) {
					in = null;
				}
			}
		}
		return listAll;
	}
	
	/**
	 * 读取Excel，并遇到空cell时，从该cell位置起遍历之前行同列的cell，直到有数据就覆盖为该cell的值
	 * @param inputStream
	 * @param firstSheet 	是否只导入第一个sheet
	 * @return
	 */
	public static List<List<String>> readExcelUsePreCell(InputStream inputStream, boolean firstSheet){
		List<List<String>> listAll = new ArrayList<List<String>>(500);
		HSSFWorkbook wb = null;
		try {
			wb = new HSSFWorkbook(inputStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
		int sheetCount = firstSheet ? 1 : wb.getNumberOfSheets();
		for(int i = 0; i < sheetCount; i++){
			HSSFSheet sheet = wb.getSheetAt(i);
			int rowCount = sheet.getLastRowNum() + 1;
			for(int j =0; j < rowCount; j++){
				HSSFRow row = sheet.getRow(j);
				if(isEmptyRow(row)){
					continue;
				}
				
				int columnCount = row.getLastCellNum();
				List<String> list = new ArrayList<String>(columnCount);
				for(int k = 0; k < columnCount; k++){
					HSSFCell cell = row.getCell(k);
					String value = null;
					value = getStringCellValue(cell);
					
					if(!StringUtil.isNulorEpt(value)){
						for(int x = j -1; x >= 0; x--){
							HSSFRow tmpRow = sheet.getRow(x);
							if(tmpRow != null){
								String tmpValue = getStringCellValue(tmpRow.getCell(k));
								if(StringUtil.isNulorEpt(tmpValue)){
									value = tmpValue;
									tmpValue = null;
									break;
								}
							}
						}
					}
					
					list.add(value);
				}
				listAll.add(list);
			}
		}
		return listAll;
	}
	
	/**
	 * 获得字符串格式的单元格数据
	 * @param cell
	 * @return
	 */
	public static String getStringCellValue(HSSFCell cell){
		String value = null;
		if(cell != null){
			switch(cell.getCellType()){
				case HSSFCell.CELL_TYPE_STRING : 
					value = cell.getRichStringCellValue().getString().trim();
					break;
				case HSSFCell.CELL_TYPE_NUMERIC : 
					if(HSSFDateUtil.isCellDateFormatted(cell)){
						value = DateUtil.formatDate(cell.getDateCellValue());
					}else {
						BigDecimal d = BigDecimal.valueOf( cell.getNumericCellValue());
						if(d.compareTo(new BigDecimal(d.intValue())) != 0){
							value = d.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
						}else{
							value = d.setScale(0, BigDecimal.ROUND_HALF_UP).toString();
						}
					}
					break;
				case HSSFCell.CELL_TYPE_BOOLEAN : 
					value = String.valueOf(cell.getBooleanCellValue());
					break;
				case HSSFCell.CELL_TYPE_FORMULA: 
					try{
						value = cell.getRichStringCellValue().getString().trim();
					}catch (IllegalStateException e) {
						try{
							value = cell.getStringCellValue();
						}catch (IllegalStateException e2) {
							double d = cell.getNumericCellValue();
							value = String.format("%.2f", d);
						}
					}
					break;
				case HSSFCell.CELL_TYPE_BLANK:
					value = "";
					break;
				case HSSFCell.CELL_TYPE_ERROR:
					value = String.valueOf(cell.getErrorCellValue());
					break;
				default: break;
			}
		}
		return value;
	}
	
	/**
	 * 判断是否空行
	 * @param row
	 * @return
	 */
	public static boolean isEmptyRow(HSSFRow row){
		if(row != null){
			for(int i = 0; i < row.getLastCellNum(); i++){
				HSSFCell cell = row.getCell(i);
				if(cell != null && !"".equals(getStringCellValue(cell))){
					return false;
				}
			}
		}
		return true;
	}
}
